package com.liu.usky.rlzp.uskyrlzp.util;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;

public class GenerateSQL {

	public static void main(String[] args) {
		GenerateSQL app = new GenerateSQL();
		app.run();
	}

	void run() {
		XSSFWorkbook book = null;
		XSSFSheet sheet = null;
		Writer createWriter = null, dropWriter = null;
		String xlsPath = "D:\\javaProject\\cqzlyd\\usky-rlzp\\src\\main\\resources\\";

		try {
			String version = "";
//			String version="V1.3";
			String xlsName = String.format("数据库设计%s.xlsx", version);// 数据库设计文件名称
			book = new XSSFWorkbook(xlsPath + xlsName);
			sheet = book.getSheetAt(0);
			String databaseName = sheet.getSheetName();

			File databseDirectory = new File(xlsPath, databaseName);
			databseDirectory.mkdirs();

			String day = new SimpleDateFormat("yyyyMMdd").format(new Date());
			File createFile = new File(databseDirectory, String.format("CreateTables%s_%s.sql", version, day));
			File dropFile = new File(databseDirectory, String.format("DropForeignKeys%s_%s.sql", version, day));

			dropWriter = new BufferedWriter(new FileWriter(dropFile, false));
			createWriter = new BufferedWriter(new FileWriter(createFile, false));
			generate(databaseName, sheet, createWriter, dropWriter);
			System.out.println("Done");
		} catch (Exception err) {
			err.printStackTrace();
			System.out.println(String.format("Error: %s", err.toString()));
		} finally {
			if (book != null) {
				try {
					book.close();
				} catch (IOException e) {
				}
			}

			if (createWriter != null) {
				try {
					createWriter.close();
				} catch (IOException e) {
				}
			}

			if (dropWriter != null) {
				try {
					dropWriter.close();
				} catch (IOException e) {
				}
			}
		}
	}

	void writeLine(Writer writer, String format, Object... args) throws IOException {
		writer.write(String.format(format, args));
		writer.write("\r\n");
		writer.flush();
	}

	Map<Integer, CellRangeAddress> createMergedIndexes(XSSFSheet sheet) {
		Map<Integer, CellRangeAddress> map = new HashMap<>();
		List<CellRangeAddress> mergeds = sheet.getMergedRegions();
		for (CellRangeAddress m : mergeds) {
			int col = m.getFirstColumn();
			if (col != 2)
				continue;

			int min = m.getFirstRow();
			int max = m.getLastRow();
			for (int i = min; i <= max; i++)
				map.put(i, m);
		}
		return map;
	}

	String safeGetString(XSSFRow row, int index) {
		return safeGetString(row.getCell(index));
	}

	String safeGetString(XSSFCell cell) {
		if (cell == null) {
			return null;
		}
		String value = cell.getStringCellValue();
		if (value == null) {
			return null;
		}
		return value.replaceAll("\r?\n", "<br />");
	}

	void generate(String dbName, XSSFSheet sht, Writer writer, Writer drpFKWriter) throws IOException {
		writeLine(writer, "drop database if exists `%s`;", dbName);
		writeLine(writer,
				"create database if not exists `%s` default character set utf8 collate utf8_general_ci;", dbName);
		writeLine(writer, "use `%s`;", dbName);
		writeLine(drpFKWriter, "use `%s`;", dbName);

		Map<Integer, CellRangeAddress> mergedIndex = createMergedIndexes(sht);

		int tableOffset = 1;

		List<String> fks = new LinkedList<>();
		List<String> indexes = new LinkedList<>();

		while (true) {
			XSSFRow row = sht.getRow(tableOffset);
			if (row == null) {
				break;
			}

			XSSFCell cell = row.getCell(1);
			CellRangeAddress mergedTableDefCell = mergedIndex.get(tableOffset);
			int columnCount = mergedTableDefCell == null ? 1 : mergedTableDefCell.getNumberOfCells();

			String tableComment = safeGetString(cell);
			if (columnCount == 1 && ("".equals(tableComment) || tableComment == null)) {
				break;
			}

			String tableName = safeGetString(row, 2);
			if (tableName == null || "".equals(tableName.trim())) {
				tableOffset += columnCount;
				continue;
			}

			List<String> pks = new ArrayList<>(columnCount);
			List<String> columns = new ArrayList<>(columnCount);
			boolean ss = false;
			for (int columnOffset = 0; columnOffset < columnCount; columnOffset++) {
				int ptr = tableOffset + columnOffset;
				XSSFRow ptrRow = sht.getRow(ptr);
				String colComment1 = safeGetString(ptrRow, 3);
				String colName = safeGetString(ptrRow, 4);
				String colDef = safeGetString(ptrRow, 5);
				String colNull = safeGetString(ptrRow, 6);
				String colMark = safeGetString(ptrRow, 7);
				String colFKTarget = safeGetString(ptrRow, 8);
				String colComment2 = safeGetString(ptrRow, 9);
				columns.add(String.format("`%s` %s %s comment \'%s %s\'", colName, colDef, colNull, colComment1,
						colComment2));
				if (colMark != null && colMark.contains("PK")) {
					ss = true;
					pks.add(colName);
				}
				if (colMark != null && colMark.contains("FK")) {
					fks.add(String.format(
							"alter table `%s` add constraint `fk_%s_%s`\r\n\tforeign key (`%s`)\r\n\treferences %s\r\n\ton delete no action\r\n\ton update no action;",
							tableName, tableName, colName, colName, colFKTarget));
					writeLine(drpFKWriter,"alter table `%s` drop foreign key `fk_%s_%s`;", tableName,
							tableName, colName);
				}
				if (colMark != null && colMark.contains("IX")) {
					indexes.add(String.format("alter table `%s` add index `index_%s_%s`(`%s`);", tableName, tableName,
							colName, colName));
				}

			}

			String bodyColumnDef = aggregate(columns, ",\r\n\t");
			String idColumns = "";
			String bodyPKDef = "";
			if (ss) {
				idColumns = aggregate(select(pks, x -> String.format("`%s`", x)), ", ");
				bodyPKDef = String.format("constraint `pk_%s` primary key (%s)", tableName, idColumns);
			}else{
				bodyColumnDef = bodyColumnDef.substring(0,bodyColumnDef.length());
			}
			StringBuilder builder = new StringBuilder();
			builder.append(String.format("create table if not exists `%s` (", tableName));
			if (bodyColumnDef != "") {
				builder.append(String.format("\r\n\t%s", bodyColumnDef));
			}
			if (bodyPKDef != "") {
				builder.append(String.format(",\r\n\r\n\t%s", bodyPKDef));
			}
			builder.append(String.format("\r\n) comment \'%s\';", tableComment));

			writeLine(writer, "");
			writeLine(writer, builder.toString());

			tableOffset += columnCount;
		}

		writeLine(writer, "");
		for (String fk : fks) {
			writeLine(writer, fk);
			writeLine(writer, "");
		}
		for (String index : indexes) {
			writeLine(writer, index);
			writeLine(writer, "");
		}
	}

	String aggregate(Collection<String> collection, String seperator) {
		StringJoiner joiner = new StringJoiner(seperator);
		for (String s : collection) {
			joiner.add(s);
		}
		return joiner.toString();
	}

	Collection<String> select(Collection<String> collection, Function<String, String> mapper) {
		LinkedList<String> list = new LinkedList<>();
		for (String s : collection) {
			list.add(mapper.apply(s));
		}
		return list;
	}

}
